import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_各学历毕业生人数及占比(
    project_id: int,
    questionnaire_ids: List[int],
    group_by_field: Optional[str] = None,
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None
) -> Dict[str, Any]:
    """
    各学历毕业生人数及占比 - 指标计算函数
    
    ## 指标说明
    该指标为客观数据指标，查询学生客观表，可按不同维度分组统计毕业生人数及占比：
    1. 按学历分组：统计不同学历（本科/硕士/博士等）的人数和占比
    2. 按性别分组：统计不同性别的毕业生人数和占比
    3. 不分组：统计总体毕业生人数
    
    计算步骤：
    1. 查询项目配置获取客户编码和项目年份
    2. 根据客户编码计算shard_tb_key用于数据筛选
    3. 查询学生客观表，按指定维度分组统计人数
    4. 计算各分组人数占比
    5. 返回分组数据和汇总数据
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        group_by_field (Optional[str]): 分组维度字段，可选值：
            - 'education': 按学历分组（默认）
            - 'sex': 按性别分组
            - None 或 'total': 不分组，统计总数
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        
    ## 示例
    ### 输入示例1：按学历分组（默认）
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159]
    }
    ```
    
    ### 输入示例2：按性别分组
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159],
        "group_by_field": "sex"
    }
    ```
    
    ### 输入示例3：总体统计
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159],
        "group_by_field": "total"
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok",
        "code": 0,
        "result": [
            {
                "dimension": "education",
                "dimension_name": "学历分布",
                "type": "grouped",
                "data": [
                    {"dimension_value": "本科毕业生", "count": 1200, "ratio": 0.6000},
                    {"dimension_value": "硕士研究生", "count": 600, "ratio": 0.3000},
                    {"dimension_value": "博士研究生", "count": 200, "ratio": 0.1000}
                ],
                "total": 2000
            },
            {
                "dimension": "total",
                "dimension_name": "总体统计",
                "type": "summary",
                "data": [
                    {"dimension_value": "总毕业生人数", "count": 2000, "ratio": 1.0000}
                ],
                "total": 2000
            }
        ]
    }
    ```
    """
    logger.info(f"开始计算指标: 各学历毕业生人数及占比, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()
        
        # 处理分组字段参数
        if not group_by_field:
            group_by_field = 'education'  # 默认按学历分组
        
        # 验证分组字段（防止SQL注入）
        allowed_fields = ['education', 'sex', 'total']
        if group_by_field not in allowed_fields:
            logger.warning(f"分组字段 {group_by_field} 不在允许列表中，使用默认值 'education'")
            group_by_field = 'education'
        
        logger.info(f"使用分组字段: {group_by_field}")

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year 
        FROM client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 构建学生表名
        student_table = f"dim_client_target_baseinfo_student_calc_{item_year}"
        
        result_list = []

        # 4. 根据分组字段查询数据
        if group_by_field == 'total':
            # 不分组，统计总数
            total_sql = f"""
            SELECT COUNT(*) as total_count
            FROM {student_table}
            WHERE shard_tb_key = %s
            """
            total_result = db.fetchone(total_sql, (shard_tb_key,))
            total_count = total_result['total_count'] if total_result else 0
            
            result_list.append({
                "dimension": "total",
                "dimension_name": "总体统计",
                "type": "summary",
                "data": [{
                    "dimension_value": "总毕业生人数",
                    "count": total_count,
                    "ratio": 1.0000
                }],
                "total": total_count
            })
            
        else:
            # 按指定维度分组统计
            dimension_name_map = {
                'education': '学历分布',
                'sex': '性别分布'
            }
            
            group_sql = f"""
            SELECT
                {group_by_field} as dimension_value, 
                COUNT(*) as count
            FROM {student_table}
            WHERE 
                shard_tb_key = %s
                AND {group_by_field} IS NOT NULL
                AND {group_by_field} != ''
            GROUP BY {group_by_field}
            ORDER BY count DESC
            """
            
            group_data = db.fetchall(group_sql, (shard_tb_key,))
            if not group_data:
                logger.warning(f"未找到项目ID={project_id}按{group_by_field}分组的学生数据")
                group_data = []

            # 计算总数和占比
            total = sum([item['count'] for item in group_data])
            formatted_data = []
            
            for item in group_data:
                ratio = item['count'] / total if total > 0 else 0
                formatted_data.append({
                    "dimension_value": item['dimension_value'],
                    "count": item['count'],
                    "ratio": round(ratio, 4)
                })

            # 添加分组数据
            result_list.append({
                "dimension": group_by_field,
                "dimension_name": dimension_name_map.get(group_by_field, f"{group_by_field}分布"),
                "type": "grouped",
                "data": formatted_data,
                "total": total
            })
            
            # 添加汇总数据
            result_list.append({
                "dimension": "total",
                "dimension_name": "总体统计",
                "type": "summary",
                "data": [{
                    "dimension_value": "总毕业生人数",
                    "count": total,
                    "ratio": 1.0000
                }],
                "total": total
            })

        logger.info(f"指标 '各学历毕业生人数及占比' 计算成功，返回 {len(result_list)} 个数据组")
        
        # 5. 构建返回结构
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": result_list
        }

    except Exception as e:
        logger.error(f"计算指标 '各学历毕业生人数及占比' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 各学历毕业生人数及占比",
            "code": 500,
            "error": str(e)
        }